﻿Imports DTO
Imports System.Data.OleDb

Public Class SachDAO
    Public Function DocFileExcel(ByVal sql As String, ByVal path As String) As DataSet
        ExcelProvider.OpenConnection(path)
        Dim data As DataSet = ExcelProvider.ExecSelect(sql)
        ExcelProvider.CloseConnection()
        Return data
    End Function

    Public Function LuuFileExcelVaoCSDL(ByVal table As DataTable) As Integer
        Dim conn As New OleDbConnection()
        conn = DataProvider.OpenConnecttion()
        Dim sql As String = "select * from SACH"
        Dim adap As New OleDbDataAdapter(sql, conn)
        Dim build As New OleDbCommandBuilder(adap)
        adap.Update(table)
    End Function

    Public Function LayDSSachTable() As DataTable
        Dim conn As OleDbConnection = DataProvider.OpenConnecttion()
        Dim sql As String = "Select * From SACH"
        Dim adap As New OleDbDataAdapter(sql, conn)
        Dim dttable As New DataTable
        adap.Fill(dttable)
        DataProvider.CloseConnect()
        Return dttable
    End Function


    Public Function LayThongTinSachTheoMaSach(ByVal maSach As Integer) As SachDTO
        DataProvider.OpenConnecttion()
        Dim sql As String = "select MaSach,TenSach,MaTheLoai,MaTacGia,NamXuatBan,MaNhaSanXuat,NgayNhap,TriGia,MaNhanVien,MaTinhTrangSach "
        sql = sql & "FROM SACH  WHERE MaSach = " & maSach.ToString()

        Dim reader As OleDbDataReader
        reader = DataProvider.ExecSelect(sql)

        Dim s As New SachDTO()

        While reader.Read()
            s.MaSach = reader.GetInt32(0)
            s.TenSach = reader.GetString(1)
            s.MaTheLoai = reader.GetInt32(2)
            s.MaTacGia = reader.GetInt32(3)
            s.NamXuatBan = reader.GetInt32(4)
            s.MaNhaSanXuat = reader.GetInt32(5)
            s.NgayNhap = reader.GetDateTime(6)
            s.TriGia = reader.GetInt32(7)
            s.MaNhanVien = reader.GetInt32(8)
            s.MaTinhTrang = reader.GetInt32(9)
        End While
        DataProvider.CloseConnect()
        Return s
    End Function

    Public Function CapNhatSach(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function



    Public Function LayDSSach() As List(Of SachDTO)
        Dim sql As String = "select MaSach,TenSach,MaTheLoai,MaTacGia,NamXuatBan,MaNhaSanXuat,NgayNhap,TriGia,MaNhanVien,MaTinhTrangSach "
        sql = sql & "from SACH"
        Dim reader As OleDbDataReader
        DataProvider.OpenConnecttion()
        reader = DataProvider.ExecSelect(sql)

        Dim dsS As New List(Of SachDTO)

        While reader.Read()
            Dim s As New SachDTO()
            s.MaSach = reader.GetInt32(0)
            s.TenSach = reader.GetString(1)
            s.MaTheLoai = reader.GetInt32(2)
            s.MaTacGia = reader.GetInt32(3)
            s.NamXuatBan = reader.GetInt32(4)
            s.MaNhaSanXuat = reader.GetInt32(5)
            s.NgayNhap = reader.GetDateTime(6)
            s.TriGia = reader.GetInt32(7)
            s.MaNhanVien = reader.GetInt32(8)
            s.MaTinhTrang = reader.GetInt32(9)
            dsS.Add(s)
        End While
        reader.Close()
        DataProvider.CloseConnect()
        Return dsS
    End Function
    Public Function TimKiem(ByVal hsCrt As SachCRT) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DataProvider.OpenConnecttion()
        Dim cmd As OleDbCommand = BuildSql(hsCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function

    Public Function BuildSql(ByVal sach As SachCRT, ByVal cn As OleDbConnection) As OleDbCommand

        Dim cmd As New OleDbCommand()
        Dim strDKMaSach = " 1=1 "
        Dim strDKTenSach = " 1=1 "
        Dim strDKNgayNhap = " 1=1 "
        Dim strDKNamXuatBan = " 1=1 "
        Dim strDKTriGia = " 1=1 "
        Dim strDKTacGia = " 1=1 "
        Dim strDKMaTheLoai = " 1=1 "
        Dim strDKMaNhaSanXuat = " 1=1 "
        Dim strDKMaTinhTrang = " 1=1 "
        Dim strDKSoLuong = " 1=1 "

        If (sach.MaSach <> 0) Then
            strDKMaSach = " MaSach = ? "
            cmd.Parameters.Add("@MaSach", OleDbType.Integer)
            cmd.Parameters("@MaSach").Value = sach.MaSach
        End If
        If (sach.TenSach <> "") Then
            strDKTenSach = " TenSach like ?"
            cmd.Parameters.Add("@TenSach", OleDbType.WChar)
            cmd.Parameters("@TenSach").Value = "%" + sach.TenSach + "%"
        End If

        If (sach.ChkNgayNhap) Then
            strDKNgayNhap = " NgayNhap between ? and ? "
            cmd.Parameters.Add("@NgayNhapTu", OleDbType.Date)
            cmd.Parameters.Add("@NgayNhapDen", OleDbType.Date)
            cmd.Parameters("@NgayNhapTu").Value = sach.NgayNhapTu
            cmd.Parameters("@NgayNhapDen").Value = sach.NgayNhapDen
        End If

        If (sach.ChkNamXuatBan) Then
            strDKNamXuatBan = " NamXuatBan between ? and ? "
            cmd.Parameters.Add("@NamXuatBanTu", OleDbType.Integer)
            cmd.Parameters.Add("@NamXuatBanDen", OleDbType.Integer)
            cmd.Parameters("@NamXuatBanTu").Value = sach.NamXuatBanTu
            cmd.Parameters("@NamXuatBanDen").Value = sach.NamXuatBanDen
        End If

        If (sach.ChkTriGia) Then
            strDKTriGia = " TriGia between ? and ? "
            cmd.Parameters.Add("@TriGiaTu", OleDbType.Integer)
            cmd.Parameters.Add("@TriGiaDen", OleDbType.Integer)
            cmd.Parameters("@TriGiaTu").Value = sach.TriGiaTu
            cmd.Parameters("@TriGiaDen").Value = sach.TriGiaDen
        End If

        If (sach.TacGia <> "" And sach.ChkTacGia) Then
            strDKTacGia = " TacGia like ?"
            cmd.Parameters.Add("@TacGia", OleDbType.WChar)
            cmd.Parameters("@TacGia").Value = "%" + sach.TacGia + "%"
        End If

        If (sach.ChkLoaiSach) Then
            strDKMaTheLoai = " MaTheLoai = ? "
            cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer)
            cmd.Parameters("@MaTheLoai").Value = sach.LoaiSach
        End If

        If (sach.ChkNhaSanXuat) Then
            strDKMaNhaSanXuat = " MaNhaSanXuat = ? "
            cmd.Parameters.Add("@MaNhaSanXuat", OleDbType.Integer)
            cmd.Parameters("@MaNhaSanXuat").Value = sach.MaNhaSanXuat
        End If

        If (sach.ChkTinhTrang) Then
            strDKMaTinhTrang = " MaTinhTrangSach = ? "
            cmd.Parameters.Add("@MaTinhTrangSach", OleDbType.Integer)
            cmd.Parameters("@MaTinhTrangSach").Value = sach.TinhTrang
        End If

        If (sach.ChkSoLuong) Then
            strDKSoLuong = " SoLuong = ? "
            cmd.Parameters.Add("@SoLuong", OleDbType.Integer)
            cmd.Parameters("@SoLuong").Value = sach.SoLuong
        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaSach
        strDKWhere += " and " + strDKTenSach
        strDKWhere += " and " + strDKNgayNhap
        strDKWhere += " and " + strDKNamXuatBan
        strDKWhere += " and " + strDKTriGia
        strDKWhere += " and " + strDKTacGia
        strDKWhere += " and " + strDKMaTheLoai
        strDKWhere += " and " + strDKMaNhaSanXuat
        strDKWhere += " and " + strDKMaTinhTrang
        strDKWhere += " and " + strDKSoLuong
        Dim strSQL As String = "Select * From SACH "
        strSQL += strDKWhere
        strSQL += " Order by MaSach "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd

    End Function


    Public Function ThemSach(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function
End Class
